package cn.stylefeng.guns.sys.core.util;

import cn.afterturn.easypoi.excel.ExcelExportUtil;
import cn.afterturn.easypoi.excel.ExcelImportUtil;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.enmus.ExcelType;
import cn.stylefeng.guns.sys.modular.system.controller.BaseFrontController;
import cn.stylefeng.guns.sys.modular.system.entity.ExcelExp;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.http.ResponseEntity;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.beans.PropertyDescriptor;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.net.URLEncoder;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.NoSuchElementException;

/**
 * @ClassName ExcelUtil
 * @Description TODO
 * @Author yuwb
 * @Date 2020-03-11 19:30
 */
@Slf4j
public class ExcelUtil {
    public static boolean isExcel2003(String filePath)
    {
        return filePath.matches("^.+\\.(?i)(xls)$");
    }

    public static boolean isExcel2007(String filePath)
    {
        return filePath.matches("^.+\\.(?i)(xlsx)$");
    }

    /**
     * 得到Workbook对象
     * @param file
     * @return
     * @throws IOException
     */
    public static Workbook getWorkBook(MultipartFile file) throws IOException {
        //这样写  excel 能兼容03和07
        InputStream is = file.getInputStream();
        Workbook hssfWorkbook = null;
        try {
            hssfWorkbook = new HSSFWorkbook(is);
        } catch (Exception ex) {
            is =file.getInputStream();
            hssfWorkbook = new XSSFWorkbook(is);
        }
        return hssfWorkbook;
    }

    /**
     * 得到错误信息
     * @param sb
     * @param list
     * @param i
     * @param obj
     * @param name  用哪个属性名去表明不和规定的数据
     * @param msg
     * @throws Exception
     */
    public static void getWrongInfo(StringBuilder sb, List list, int i, Object obj, String name, String msg) throws Exception{
        Class clazz=obj.getClass();
        Object str=null;
        //得到属性名数组
        Field[] fields = clazz.getDeclaredFields();
        for(Field f : fields){
            if(f.getName().equals(name)){
                //用来得到属性的get和set方法
                PropertyDescriptor pd = new PropertyDescriptor(f.getName(), clazz);
                //得到get方法
                Method getMethod=pd.getReadMethod();
                str = getMethod.invoke(obj);
            }
        }
        if(i==0) {
            sb.append(msg + str + ";");
        }
        else if(i==(list.size()-1)) {
            sb.append(str + "</br>");
        }
        else {
            sb.append(str + ";");
        }
    }


    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName,boolean isCreateHeader, HttpServletResponse response){
        ExportParams exportParams = new ExportParams(title, sheetName);
        exportParams.setCreateHeadRows(isCreateHeader);
        defaultExport(list, pojoClass, fileName, response, exportParams);

    }
    public static void exportExcel(List<?> list, String title, String sheetName, Class<?> pojoClass,String fileName, HttpServletResponse response){
        defaultExport(list, pojoClass, fileName, response, new ExportParams(title, sheetName));
    }
    public static void exportExcel(List<Map<String, Object>> list, String fileName, HttpServletResponse response){
        defaultExport(list, fileName, response);
    }

    private static void defaultExport(List<?> list, Class<?> pojoClass, String fileName, HttpServletResponse response, ExportParams exportParams) {
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams,pojoClass,list);
        if (workbook != null){
            downLoadExcel(fileName, response, workbook);
        }

    }

    public static void downLoadExcel(String fileName, HttpServletResponse response, Workbook workbook) {
        try {
            // 告诉浏览器用什么软件可以打开此文件
            response.setHeader("content-Type", "application/vnd.ms-excel");
            //设置浏览器响应头对应的Content-disposition
            response.setHeader("Content-Disposition",
                    "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8"));
            //编码
            response.setCharacterEncoding("UTF-8");
            workbook.write(response.getOutputStream());
        } catch (IOException e) {
            e.printStackTrace();
        }finally {
            try {
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }
    private static void defaultExport(List<Map<String, Object>> list, String fileName, HttpServletResponse response) {
        Workbook workbook = ExcelExportUtil.exportExcel(list, ExcelType.HSSF);
        if (workbook != null){
            downLoadExcel(fileName, response, workbook);
        }

    }

    public static <T> List<T> importExcel(String filePath,Integer titleRows,Integer headerRows, Class<T> pojoClass){
        if (StringUtils.isBlank(filePath)){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(new File(filePath), pojoClass, params);
        }catch (NoSuchElementException e){
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    public static <T> List<T> importExcel(MultipartFile file, Integer titleRows, Integer headerRows, Class<T> pojoClass){
        if (file == null){
            return null;
        }
        ImportParams params = new ImportParams();
        params.setTitleRows(titleRows);
        params.setHeadRows(headerRows);
        List<T> list = null;
        try {
            list = ExcelImportUtil.importExcel(file.getInputStream(), pojoClass, params);
        }catch (NoSuchElementException e){
            e.printStackTrace();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     *
     * @Title: exportManySheetExcel
     * @Description: 可生成单个、多个sheet
     * @param @param file 导出文件路径
     * @param @param mysheets
     * @return void
     * @throws
     */
    public static void exportManySheetExcel(String file, List<ExcelExp> mysheets){

        HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄
        List<ExcelExp> sheets = mysheets;



        //表头样式
        HSSFCellStyle style = wb.createCellStyle();
        //style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        //字体样式
        HSSFFont fontStyle = wb.createFont();
        fontStyle.setFontName("微软雅黑");
        fontStyle.setFontHeightInPoints((short)12);
        //fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(fontStyle);
//        style.setFillPattern(FillPatternType.BIG_SPOTS);
//        style.setFillForegroundColor(IndexedColors.RED.getIndex());
//        style.setFillBackgroundColor(IndexedColors.LIGHT_BLUE.getIndex());

        for(ExcelExp excel: sheets){
            //新建一个sheet
            HSSFSheet sheet = wb.createSheet(excel.getFileName());//获取该sheet名称

            String[] handers = excel.getHanders();//获取sheet的标题名
            HSSFRow rowFirst = sheet.createRow(0);//第一个sheet的第一行为标题
            //写标题
            for(int i=0;i<handers.length;i++){
                //获取第一行的每个单元格
                HSSFCell cell = rowFirst.createCell(i);
                //往单元格里写数据
                cell.setCellValue(handers[i]);
                cell.setCellStyle(style); //加样式
                sheet.setColumnWidth(i, 4000); //设置每列的列宽
            }

            //写数据集
            List<String[]> dataset = excel.getDataset();
            for(int i=0;i<dataset.size();i++){
                String[] data = dataset.get(i);//获取该对象

                //创建数据行
                HSSFRow row = sheet.createRow(i+1);

                for(int j=0;j<data.length;j++){
                    //设置对应单元格的值
                    row.createCell(j).setCellValue(data[j]);
                }
            }
        }

        // 写文件
        try {

            FileOutputStream out = new FileOutputStream(new File(file));
            out.flush();
            wb.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }


    public static void exportOneSheetExcel(String file, ExcelExp excel){

        HSSFWorkbook wb = new HSSFWorkbook();//创建工作薄
        //List<ExcelExp> sheets = mysheets;



        //表头样式
        HSSFCellStyle style = wb.createCellStyle();
        //style.setAlignment(HSSFCellStyle.ALIGN_CENTER); // 创建一个居中格式
        //字体样式
        HSSFFont fontStyle = wb.createFont();
        fontStyle.setFontName("微软雅黑");
        fontStyle.setFontHeightInPoints((short)12);
        //fontStyle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        style.setFont(fontStyle);
//        style.setFillPattern(FillPatternType.BIG_SPOTS);
//        style.setFillForegroundColor(IndexedColors.RED.getIndex());
//        style.setFillBackgroundColor(IndexedColors.LIGHT_BLUE.getIndex());

        //for(ExcelExp excel: sheets){
            //新建一个sheet
            HSSFSheet sheet = wb.createSheet(excel.getFileName());//获取该sheet名称

            String[] handers = excel.getHanders();//获取sheet的标题名
            HSSFRow rowFirst = sheet.createRow(0);//第一个sheet的第一行为标题
            //写标题
            for(int i=0;i<handers.length;i++){
                //获取第一行的每个单元格
                HSSFCell cell = rowFirst.createCell(i);
                //往单元格里写数据
                cell.setCellValue(handers[i]);
                cell.setCellStyle(style); //加样式
                sheet.setColumnWidth(i, 4000); //设置每列的列宽
            }

            //写数据集
            List<String[]> dataset = excel.getDataset();
            for(int i=0;i<dataset.size();i++){
                String[] data = dataset.get(i);//获取该对象

                //创建数据行
                HSSFRow row = sheet.createRow(i+1);

                for(int j=0;j<data.length;j++){
                    //设置对应单元格的值
                    row.createCell(j).setCellValue(data[j]);
                }
            }
       // }

        // 写文件
        try {

            FileOutputStream out = new FileOutputStream(new File(file));
            out.flush();
            wb.write(out);
            out.close();
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
    /**
     *
     * @Title: getExcel
     * @Description: 下载指定路径的Excel文件
     * @param @param url 文件路径
     * @param @param fileName  文件名
     * @param @param response
     * @return void
     * @throws
     */
    public static void getExcel(String url, String fileName, HttpServletResponse response){

        try {

            //1.设置文件ContentType类型，这样设置，会自动判断下载文件类型
            response.setContentType("multipart/form-data");

            //2.设置文件头：最后一个参数是设置下载文件名
            response.setHeader("Content-Disposition", "attachment;filename="
                    + new String(fileName.getBytes("gb2312"), "ISO-8859-1") + ".xls"); //支持中文文件名

            //通过文件路径获得File对象
            File file = new File(url);

            FileInputStream in = new FileInputStream(file);
            //3.通过response获取OutputStream对象(out)
            OutputStream out = new BufferedOutputStream(response.getOutputStream());

            int b = 0;
            byte[] buffer = new byte[2048];
            while ((b=in.read(buffer)) != -1){
                out.write(buffer,0,b); //4.写到输出流(out)中
            }

            in.close();
            out.flush();
            out.close();

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     *
     * @param filePath  需要读取的文件路径
     * @param column  指定需要获取的列数，例如第一列 1
     * @param startRow 指定从第几行开始读取数据
     * @param  endRow 指定结束行
     * @return 返回读取列数据的set
     */
    public static HashSet<String> getColumnSet(String filePath, int column, int startRow , int endRow){
        Workbook wb = readExcel(filePath); //文件
        Sheet sheet = wb.getSheetAt(0); //sheet
        int rownum = sheet.getPhysicalNumberOfRows(); //行数
        Row row = null;
        HashSet<String> result = new HashSet<>();
        String cellData = null;
        if(wb != null){
            for (int i=startRow-1; i<endRow; i++){
                System.out.println(i);
                row = sheet.getRow(i);
                if(row !=null){
                    cellData = (String) getCellFormatValue(row.getCell(column-1));
                    result.add(cellData.replaceAll(" ", ""));
                }else{
                    break;
                }
                System.out.println(cellData);
            }
        }
        return  result;
    }

    /**
     *
     * @param filePath 需要读取的文件路径
     * @param column 指定需要获取的列数，例如第一列 1
     * @param startRow 指定从第几行开始读取数据
     * @return  返回读取列数据的set
     */
    public static HashSet<String> getColumnSet(String filePath, int column, int startRow){
        Workbook wb = readExcel(filePath); //文件
        Sheet sheet = wb.getSheetAt(0); //sheet
        int rownum = sheet.getPhysicalNumberOfRows(); //行数
        System.out.println("sumrows " + rownum);

        return  getColumnSet(filePath, column, startRow , rownum-1);
    }



    //读取excel
    public static Workbook readExcel(String filePath){
        Workbook wb = null;
        if(filePath==null){
            return null;
        }
        String extString = filePath.substring(filePath.lastIndexOf("."));
        InputStream is = null;
        try {
            is = new FileInputStream(filePath);
            if(".xls".equals(extString)){
                return wb = new HSSFWorkbook(is);
            }else if(".xlsx".equals(extString)){
                return wb = new XSSFWorkbook(is);
            }else{
                return wb = null;
            }
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return wb;
    }

    public static Object getCellFormatValue(Cell cell){
        Object cellValue = null;
        if(cell!=null){
            //判断cell类型
            switch(cell.getCellType()){
                case NUMERIC:{
                    cell.setCellType(CellType.STRING);  //将数值型cell设置为string型
                    cellValue = cell.getStringCellValue();
                    break;
                }
                case FORMULA:{
                    //判断cell是否为日期格式
//                    if(DateUtil.isCellDateFormatted(cell)){
//                        //转换为日期格式YYYY-mm-dd
//                        cellValue = cell.getDateCellValue();
//                    }else{
                        //数字
                        cellValue = String.valueOf(cell.getNumericCellValue());
                    //}
                    break;
                }
                case STRING:{
                    cellValue = cell.getRichStringCellValue().getString();
                    break;
                }
                default:
                    cellValue = "";
            }
        }else{
            cellValue = "";
        }
        return cellValue;
    }


    /**
     * 按列输出excel
     * @param request
     * @param response
     * @param biaotiCol
     * @param conCol
     * @return
     */
    public ResponseEntity<byte[]> exportExcelByCol(HttpServletRequest request, HttpServletResponse response, List<String>biaotiCol, List<String> conCol) {
        try {
            //TODO-1 要导出的数据内容
            BaseFrontController baseFrontController = new BaseFrontController();

            return baseFrontController.buildResponseEntity(exportExcel(biaotiCol,   conCol), "测试.xls");
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }


    private InputStream exportExcel(List<String>biaotiCol,  List<String> conCol) {
        ByteArrayOutputStream output = null;
        InputStream inputStream1 = null;
        // 保留1000条数据在内存中
        SXSSFWorkbook wb = new SXSSFWorkbook(1000);
        SXSSFSheet sheet = wb.createSheet();
        // 设置报表头样式cell样式
        CellStyle header = ExcelFormatUtil.headSytle(wb);

        for(int i=0;i<biaotiCol.size();i++){
            SXSSFRow row1 = sheet.createRow(i);

            SXSSFCell cell0 = row1.createCell(0);
            cell0.setCellValue(biaotiCol.get(i));
            cell0.setCellStyle(header);

            SXSSFCell cell01 = row1.createCell(1);
            cell01.setCellValue(conCol.get(i));
            cell01.setCellStyle(header);
        }

        //TODO-4 设置内容标题
        //内容的标题所在行
        int concentitleStartRow = 6;
        //设置表头样式
        //ExcelFormatUtil.initContentTitleEX(sheet, header, 1, titles, ints);
        //logger.info(">>>>>>>>>>>>>>>>>>>>表头样式设置完成>>>>>>>>>>");

        try {
            output = new ByteArrayOutputStream();
            wb.write(output);
            inputStream1 = new ByteArrayInputStream(output.toByteArray());
            output.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {

            try {
                if (output != null) {
                    output.close();
                    if (inputStream1 != null) {
                        inputStream1.close();
                    }
                }
            } catch (IOException e) {
                e.printStackTrace();
            }

        }
        return inputStream1;
    }


}
